﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class PhongDAO
    {
        public class SqlConnectionData
        {
            public static SqlConnection Connection()
            {
                SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=QLKS;Integrated Security=True");
                return conn;
            }
        }
        public static void UpdateTinhTrangPhong(int maphong,int tinhtrang)
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_UpdateTinhTrangPhong", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
            cmd.Parameters.Add("@TinhTrangPhong", SqlDbType.Int);
            cmd.Parameters["@MaPhong"].Value = maphong;
            cmd.Parameters["@TinhTrangPhong"].Value = tinhtrang;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public static DataTable PhongTheoMaLoaiPhong(int maloaiphong)
        {
            string sql = string.Format("Select * From PHONG where MaLoaiPhong= " + maloaiphong);
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
         public static DataTable PhongTheoTen(string tenphong)
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_PhongTheoTen", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar,50);
            cmd.Parameters["@TenPhong"].Value = tenphong;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
         public static DataTable PhongTheoLoai(string loai)
         {
             string sql = string.Format("Select * From LOAIPHONG l, PHONG p Where l.MaLoaiPhong=p.MaLoaiPhong and TenLoaiPhong like '" + loai + "' and TinhtrangPhong=1");
             DataTable dt = DataProvider.ExecuteQuery(sql);
             return dt;
         }
         public static DataTable GetAll()
         {
             SqlConnection conn = SqlConnectionData.Connection();
             SqlCommand cmd = new SqlCommand("sp_PhongGetAll", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             SqlDataAdapter da = new SqlDataAdapter(cmd);
             DataTable dt = new DataTable();
             da.Fill(dt);
             return dt;
         }
         public static void InsertPhong(PhongDTO p)
         {
             SqlConnection conn = SqlConnectionData.Connection();
             SqlCommand cmd = new SqlCommand("sp_InsertPhong", conn);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar ,50);
             cmd.Parameters.Add("@MaLoaiPhong", SqlDbType.Int);
             cmd.Parameters.Add("@TinhTrangPhong", SqlDbType.Int);
             cmd.Parameters.Add("@GhiChu", SqlDbType.NVarChar,500);
             cmd.Parameters["@TenPhong"].Value = p.TenPhong;
             cmd.Parameters["@MaLoaiPhong"].Value = p.MaLoaiPhong;
             cmd.Parameters["@TinhTrangPhong"].Value = p.TinhTrangPhong;
             cmd.Parameters["@GhiChu"].Value = p.GhiChu;
             conn.Open();
             cmd.ExecuteNonQuery();
             conn.Close();
         }
         public static void XoaPhong(int maphong)
         {
             string sql = string.Format("Delete From Phong Where MaPhong = " + maphong + "");
             DataProvider.ExecuteNonQuery(sql);
         }

    }
}
